﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Web;
using System.Web.UI.MobileControls;
using LumenWorks.Framework.IO.Csv;

namespace EximDemo.Models
{
    public class BizImport
    {
        private const int BLOCK_COMMIT = 4000;
        private EximModelDataContext _context = new EximModelDataContext();
        public string UploadFileName { get; set; }

        public BizImport() { }

        public BizImport(string path){
            this.UploadFileName = path;
        }

        public void RunImport()
        {
            

            //debug
            DateTime start = DateTime.Now;

            TextReader uploadData = new StreamReader(this.UploadFileName, Encoding.UTF8);
            using (CachedCsvReader csv = new CachedCsvReader(uploadData, false))
            {
                if (!ValidateFileStructure(csv)) return;

                
                List<productrangestatus> lstImport = new List<productrangestatus>();
                int count = 0;
                string contains = string.Empty;

                csv.SkipEmptyLines = true;                
                while (csv.ReadNextRecord())
                {
                    try
                    {
                        productrangestatus data = new productrangestatus();
                        data.storeId = int.Parse(csv[0]);
                        data.productId = int.Parse(csv[1]);
                        data.status = csv[2];
                        data.range = csv[3];
                        
                        contains += (string.IsNullOrEmpty(contains) == true ? "" : ",") + string.Format("'{0}'", csv[0] + "_" + csv[1]);
                        lstImport.Add(data);
                        
                        count++;
                        if (count == BLOCK_COMMIT)
                        {
                            ExecuteImport(lstImport, contains);

                            count = 0; //reset count
                            contains = string.Empty;
                        }
                    }
                    catch
                    {
                        count = 0; //reset count
                    }
                }

                if (count > 0)
                {
                    ExecuteImport(lstImport, contains);
                }
            }

            //debug
            DateTime end = DateTime.Now;
            Debug.Print("Executed duration: " + (end - start).ToString());
        }

        #region Helpers
        private void ExecuteImport(List<productrangestatus> lstImport, string contains)
        {
            StringBuilder sb = new StringBuilder();
            string sql = "SELECT * FROM productrangestatus WHERE cast(storeId as varchar(20)) + '_' + cast(productId as varchar(20)) IN (" + contains + ")";
            List<productrangestatus> lstExist = _context.ExecuteQuery<productrangestatus>(sql).ToList();
            foreach (productrangestatus p in lstExist)
            {
                sb.AppendLine(BuildUpdateSQL(p));
            }

            lstImport.RemoveAll(p => lstExist.Where(x => x.storeId == p.storeId && x.productId == p.productId).Count() > 0);
            foreach (productrangestatus p in lstImport)
            {
                sb.AppendLine(BuildInsertSQL(p));
            }

            ThreadPool.QueueUserWorkItem(RunThread, sb.ToString());
        }

        public void RunThread(Object threadContext)
        {
            ExecuteCommandSQL((string)threadContext);
        }

        public void ExecuteCommandSQL(string sql)
        {
            using (EximModelDataContext ctx = new EximModelDataContext())
            {
                try
                {
                    ctx.CommandTimeout = 0; //unlimit timeout
                    ctx.ExecuteCommand(sql);                    
                }
                catch { }
            }
        }

        private string BuildInsertSQL(productrangestatus data)
        {
            string sql = @" IF (EXISTS(SELECT 1 FROM store WHERE storeId = {0}) AND EXISTS(SELECT 1 FROM product WHERE productId = {1})) 
                            BEGIN
                                INSERT INTO productrangestatus VALUES({0}, {1}, '{2}', '{3}');
                            END ";
            return string.Format(sql, data.storeId.ToString(), data.productId.ToString(), data.status.Replace("'", "''"), data.range.Replace("'", "''"));
        }

        private string BuildUpdateSQL(productrangestatus data)
        {
            string sql = "UPDATE productrangestatus SET status = '{2}', range = '{3}' WHERE storeId = {0} AND productId = {1};";
            return string.Format(sql, data.storeId.ToString(), data.productId.ToString(), data.status.Replace("'", "''"), data.range.Replace("'", "''"));
        }

        private bool ValidateFileStructure(CachedCsvReader csv)
        {
            return (csv.FieldCount == 4);
        } 
        #endregion
    }
}